import pymysql

connection = pymysql.connect(host='127.0.0.1', user='root', password='777271ll',db='schooldata')
cur=connection.cursor()
files=['department','teacher','student', 'course','class','course_selection']
heads={"department":'(dept_id, dept_name, address, phone_code)',
       "student":'(sname, student_id, grade, sex, native_place, mobile_phone, dept_id)',
       "teacher":'(tname, staff_id, sex, professional_ranks, dept_id)',
       "course":'(course_id, course_name, credit, credit_hours, dept_id)',
       "class":'(semester, course_id, staff_id, class_time)',
       "course_selection":'(student_id, semester, course_id, staff_id, normal_score, test_score, total_score)'
       }
for f in files:
    with open(f'sql_{f}.txt', 'r', encoding='utf-8') as file:
        sql_content = file.readlines()
        sql='insert into '+f+heads[f]+' values '
        for line in sql_content:
            sql=sql+line
        print(sql)
        cur.execute(sql)

        connection.commit()

sql='''UPDATE course_selection AS cs
JOIN student AS s ON cs.student_id = s.student_id
SET cs.gpa = CASE
    WHEN total_score >= 90 THEN 4.0
    WHEN total_score >= 85 THEN 3.7
    WHEN total_score >= 82 THEN 3.3
    WHEN total_score >= 78 THEN 3.0
    WHEN total_score >= 75 THEN 2.7
    WHEN total_score >= 72 THEN 2.3
    WHEN total_score >= 68 THEN 2.0
    WHEN total_score >= 66 THEN 1.7
    WHEN total_score >= 64 THEN 1.5
    WHEN total_score >= 60 THEN 1.0
    ELSE 0.0
    END
    WHERE s.student_id != NULL;
    '''
cur.execute(sql)
connection.commit()
cur.close()
connection.close()